package com.icss.day07.JDBCtest;


import com.icss.day07.vo.Employees;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test35 {
    public static void main(String[] args) {

        List<Employees> elist = new ArrayList<>();
        // 1. 加载驱动
        // 获取反射， 加载某个类
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建链接
            conn = DriverManager.getConnection("jdbc:mysql://172.16.106.128:3306/myemployees", "root", "123456");

            // 3. 创建执行器
            StringBuilder sb = new StringBuilder();
            sb.append("\n" +
                    "SELECT e.employee_id,\n" +
                    "       e.first_name,\n" +
                    "       e.last_name,\n" +
                    "       e.email,\n" +
                    "       e.phone_number,\n" +
                    "       e.job_id,\n" +
                    "       e.salary,\n" +
                    "       e.commission_pct,\n" +
                    "       e.manager_id,\n" +
                    "       e.department_id,\n" +
                    "       e.hiredate from employees e ");


            PreparedStatement ps = conn.prepareStatement(sb.toString());


            // 4. 执行SQL
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Employees row = new Employees();
                elist.add(row);

                row.setEmployeeId(rs.getInt(1));
                row.setFirstName(rs.getString(2));
                row.setLastName(rs.getString(3));
                row.setEmail(rs.getString("email"));
                row.setPhoneNumber(rs.getString("phone_number"));
                row.setSalary(rs.getDouble("salary"));
                row.setCommissionPct(rs.getDouble("commission_pct"));
                row.setJobId(rs.getString("job_id"));
                row.setDepartmentId(rs.getInt("department_id"));
                row.setManagerId(rs.getInt("manager_id"));
                row.setHiredate(rs.getTimestamp("hiredate"));

            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (conn != null) {
                try {

                    // 5. 释放资源
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }

        // 都完成之后
        elist.forEach(System.out::println);

    }
}
